package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.SetorCategoriaEntity;
import br.com.skytef.entity.SetorEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class SetorDao {
	public Connection connection;
	private static String erro;

	public SetorDao() {
		this.connection = new ConnectionFactory().getConnection();
	}

	public SetorDao(HttpSession session) {
		this.connection = utils.recoverSessionDBConnection(session);
	}

	public SetorDao(Connection con) {
		this.connection = con;
	}

	public Connection getConnection() {
		return connection;
	}

	/*----------------------------------------------------------------------------
	 * Exec
	 ----------------------------------------------------------------------------*/
	public List<SetorEntity> selectSetorAll() throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement("SELECT * FROM setor");
		List<SetorEntity> result = this.execSelectSetor(stmt);
		stmt.close();
		connection.close();
		return result;
	} 
	
	public List<SetorEntity> selectSetorByPos(int idPos) throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectByPos());
		stmt.setInt(1, idPos);
		List<SetorEntity> result = this.execSelectSetor(stmt);
		stmt.close();
		connection.close();
		return result;
	} 
	
	public List<SetorEntity> selectSetorById(int idSetor) throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectSetorCategBySetor());
		stmt.setInt(1, idSetor);
		List<SetorEntity> result = this.execSelectSetor(stmt);
		stmt.close();
		connection.close();
		return result;
	} 
	
	private List<SetorEntity> execSelectSetor(PreparedStatement stmt) throws Exception {
		List<SetorEntity> setores = new ArrayList<SetorEntity>();

		try {
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				SetorEntity setor = new SetorEntity();

				setor.setIdSetor(rs.getInt("idSetor"));
				setor.setIdEmpresa(rs.getInt("idEmpresa"));
				setor.setDescricao(rs.getString("Descricao"));
				setores.add(setor);
			}
			rs.close();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}
		return setores;
	}
	/*----------------------------------------------------------------------------
	 * Exec
	 ----------------------------------------------------------------------------*/
	public List<SetorCategoriaEntity> selectSetorCategory() throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectSetorCategAll());
		List<SetorCategoriaEntity> result = this.execSetorCategory(stmt);
		stmt.close();
		connection.close();
		return result;
	}
	
	public List<SetorCategoriaEntity> selectSetorCategoryById(int idSetor) throws Exception {
		Connection connection = new ConnectionFactory().getConnection();
		PreparedStatement stmt = connection.prepareStatement(this.getQrySelectSetorCategBySetor());
		stmt.setInt(1, idSetor);
		List<SetorCategoriaEntity> result = this.execSetorCategory(stmt);
		stmt.close();
		connection.close();
		return result;
	}
		
	private List<SetorCategoriaEntity> execSetorCategory(PreparedStatement stmt) throws Exception {
		List<SetorCategoriaEntity> setCats = new ArrayList<SetorCategoriaEntity>();

		try {
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				SetorCategoriaEntity setCat = new SetorCategoriaEntity();

				setCat.setIdSetor(rs.getInt("idSetor"));
				setCat.setIdCategoria(rs.getInt("idCategoria"));
				setCat.setDesc(rs.getString("Descricao"));
				setCats.add(setCat);
			}
			rs.close();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}
		return setCats;
	}
	/*----------------------------------------------------------------------------
	 * Exec Delete
	 ----------------------------------------------------------------------------*/
	public void deleteSetorCategoryById(int idSetor) throws Exception {
		PreparedStatement stmt = connection.prepareStatement(this.getQryDeleteSetorCategBySetor());
		try {
			stmt.setInt(1, idSetor);
			stmt.execute();	
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}finally{
			stmt.close();
		}
	}
	
	/*----------------------------------------------------------------------------
	 * Exec Insert
	 ----------------------------------------------------------------------------*/
	public void insertSetorCategory(SetorCategoriaEntity setorCateg) throws Exception {
		PreparedStatement stmt = connection.prepareStatement(this.getQryInsertSetorCateg());
		try {
			stmt.setInt(1, setorCateg.getIdSetor());
			stmt.setInt(2, setorCateg.getIdCategoria());
			stmt.setString(3, setorCateg.getDesc());
			stmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}finally{
			stmt.close();	
		}
	}
	
	public int insertSetor(SetorEntity setor) throws Exception {
		PreparedStatement stmt = connection.prepareStatement(this.getQryInsertSetor());
		int idSetor = 0;
		
		try {
			stmt.setInt(1, setor.getIdEmpresa());
			stmt.setString(2, setor.getDescricao());
			stmt.execute();	
			
			
			PreparedStatement stmt2 = connection.prepareStatement("SELECT Last_insert_id() as id FROM setor");
			ResultSet rs = stmt2.executeQuery();
			
			if (rs.next()) {
				idSetor = rs.getInt("id");
			}
			
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}finally{
			stmt.close();	
		}
		return idSetor;
	}
	
	/*----------------------------------------------------------------------------
	 * Exec Update
	 ----------------------------------------------------------------------------*/
	public void updateSetor(SetorEntity setor) throws Exception {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE setor SET");
		sql.append("       idEmpresa = ?, ");
		sql.append("       Descricao = ? ");
		sql.append("WHERE  idSetor = ?;");
		
		
		PreparedStatement stmt = connection.prepareStatement(sql.toString());
		try {			
			stmt.setInt(1, setor.getIdEmpresa());
			stmt.setString(2, setor.getDescricao());
			stmt.setInt(3, setor.getIdSetor());
			stmt.execute();		
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}finally{
			stmt.close();	
		}
	}
	/*--------------------------------------------------------------------------------------------
	 * Query
	 --------------------------------------------------------------------------------------------*/		
	private String getQrySelectSetorCategBySetor() {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT * FROM setor_categoria ");
		sql.append("WHERE idSetor = ?;");

		return sql.toString();
	}
	
	private String getQrySelectSetorCategAll() {

		StringBuffer sql = new StringBuffer();

		sql.append("SELECT * FROM setor_categoria ");

		return sql.toString();
	}
	
	private String getQryInsertSetorCateg() {

		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO setor_categoria ");
		sql.append("            (idsetor, ");
		sql.append("             idcategoria, ");
		sql.append("             descricao) ");
		sql.append("VALUES      (?, ?, ?);   ");

		return sql.toString();
	}
	
	private String getQryInsertSetor() {

		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO setor");
		sql.append("            (idEmpresa, ");
		sql.append("             Descricao) ");
		sql.append("VALUES      (?, ?); ");

		return sql.toString();
	}
	
	private String getQryUpdateSetor() {

		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE setor SET");
		sql.append("       idEmpresa = ?, ");
		sql.append("       Descricao = ? ");
		sql.append("WHERE  idSetor = ?;");

		return sql.toString();
	}
	
	private String getQryDeleteSetorCategBySetor() {

		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM setor_categoria ");
		sql.append("WHERE idSetor = ?;");

		return sql.toString();
	}
	
	private String getQrySelectByPos() {

        StringBuffer sql = new StringBuffer();

        sql.append("SELECT * FROM setor LEFT JOIN pos_setor ");
        sql.append("                    ON setor.idSetor = pos_setor.fk_idSetor ");
        sql.append("WHERE pos_setor.fk_idPos = ?;");

        return sql.toString();

}

}
